package zy.dao.sell.cart.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.cart.CartDAO;
import zy.entity.sell.cart.T_Sell_Cart;
import zy.entity.sell.cart.T_Sell_CartList;
import zy.util.CommonUtil;
import zy.util.DateUtil;

@Repository
public class CartDAOImpl extends BaseDaoImpl implements CartDAO{

	@Override
	public List<T_Sell_CartList> temp_list(String scl_em_code, String scl_shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT scl_id,scl_number,scl_sysdate,scl_pd_code,scl_sub_code,scl_cr_code,scl_sz_code,scl_br_code,");
		sql.append(" scl_amount,scl_sell_price,scl_state,scl_vm_code,scl_em_code,scl_shop_code,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pd_img_path,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = scl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = scl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = scl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sell_cartlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.scl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND scl_state = 0");
		sql.append(" AND scl_em_code = :scl_em_code");
		sql.append(" AND scl_shop_code = :scl_shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY scl_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("scl_em_code", scl_em_code).addValue("scl_shop_code", scl_shop_code).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sell_CartList.class));
	}
	
	@Override
	public void updateAmount(Integer scl_amount,Long scl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_cartlist");
		sql.append(" SET scl_amount = :scl_amount");
		sql.append(" WHERE scl_id = :scl_id");
		namedParameterJdbcTemplate.update(sql.toString(), 
				new MapSqlParameterSource().addValue("scl_amount", scl_amount).addValue("scl_id", scl_id));
	}
	
	@Override
	public T_Sell_CartList loadCartList(String sub_code,String em_code,String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT scl_id,scl_number,scl_sysdate,scl_pd_code,scl_sub_code,scl_cr_code,scl_sz_code,scl_br_code,");
		sql.append(" scl_amount,scl_sell_price,scl_state,scl_vm_code,scl_em_code,scl_shop_code,t.companyid");
		sql.append(" FROM t_sell_cartlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND scl_state = 0");
		sql.append(" AND scl_sub_code = :scl_sub_code");
		sql.append(" AND scl_em_code = :scl_em_code");
		sql.append(" AND scl_shop_code = :scl_shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("scl_sub_code", sub_code)
							.addValue("scl_em_code", em_code)
							.addValue("scl_shop_code", shop_code)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_CartList.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void saveCartList(T_Sell_CartList cartList) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_cartlist");
		sql.append(" (scl_number,scl_sysdate,scl_pd_code,scl_sub_code,scl_cr_code,scl_sz_code,scl_br_code,scl_amount,scl_sell_price,");
		sql.append(" scl_state,scl_vm_code,scl_em_code,scl_shop_code,companyid)");
		sql.append(" VALUES(:scl_number,:scl_sysdate,:scl_pd_code,:scl_sub_code,:scl_cr_code,:scl_sz_code,:scl_br_code,:scl_amount,:scl_sell_price,");
		sql.append(" :scl_state,:scl_vm_code,:scl_em_code,:scl_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(cartList),holder);
		cartList.setScl_id(holder.getKey().longValue());
	}
	
	@Override
	public void delCartList(Long scl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_cartlist");
		sql.append(" WHERE scl_id=:scl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("scl_id", scl_id));
	}
	
	@Override
	public void clearCartList(String em_code,String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_cartlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND scl_state = 0");
		sql.append(" AND scl_em_code = :scl_em_code");
		sql.append(" AND scl_shop_code = :scl_shop_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("scl_em_code", em_code)
						.addValue("scl_shop_code", shop_code)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void updateCartListVip(String vm_code, String em_code, String shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_cartlist");
		sql.append(" SET scl_vm_code = :scl_vm_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND scl_state = 0");
		sql.append(" AND scl_em_code = :scl_em_code");
		sql.append(" AND scl_shop_code = :scl_shop_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("scl_em_code", em_code)
						.addValue("scl_vm_code", vm_code)
						.addValue("scl_shop_code", shop_code)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void saveCart(T_Sell_Cart cart, List<T_Sell_CartList> cartLists) {
		String prefix = CommonUtil.NUMBER_PREFIX_SELL_CART + cart.getSc_shop_code()+cart.getSc_em_code() + DateUtil.getYearMonthDateYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(sc_number))) AS new_number");
		sql.append(" FROM t_sell_cart");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(sc_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", cart.getCompanyid()), String.class);
		cart.setSc_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_sell_cart");
		sql.append(" (sc_vm_code,sc_number,sc_em_code,sc_shop_code,sc_sysdate,sc_amount,sc_money,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sc_vm_code,:sc_number,:sc_em_code,:sc_shop_code,:sc_sysdate,:sc_amount,:sc_money,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(cart),holder);
		cart.setSc_id(holder.getKey().intValue());
		for(T_Sell_CartList item:cartLists){
			item.setScl_number(cart.getSc_number());
		}
		sql.setLength(0);
		sql.append(" UPDATE t_sell_cartlist");
		sql.append(" SET scl_number = :scl_number");
		sql.append(" ,scl_state = 1");
		sql.append(" WHERE scl_id = :scl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(cartLists.toArray()));
	}
	
}
